package com.muchenx.db;

import com.muchenx.db.annotation.Column;
import com.muchenx.db.annotation.Id;
import com.muchenx.db.annotation.Table;
import com.muchenx.util.Assert;
import com.muchenx.util.DBUtil;
import com.muchenx.util.ReflectUtil;
import com.muchenx.util.UIComponentUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

/**
 * 实体数据库表映射处理工具
 */
@Slf4j
public final class SqlHelper {
    private final static String ENTITY_MAPPER_PREFIX = "ENTITY_MAPPER_PREFIX_";
    private final static Map<String, Map<String, Field>> CACHE = new ConcurrentHashMap<>();

    @SuppressWarnings("all")
    public static synchronized <T> T getOne(Serializable where) {
        return (T) getOne(where.getClass(), prepareQuerySql(where));
    }

    @SuppressWarnings("all")
    public static synchronized <T> T getOne(Class<T> entityCls, String sql, Object... params) {
        List<T> list = list(entityCls, sql, params);
        return list.isEmpty() ? null : list.get(0);
    }

    @SuppressWarnings("all")
    public static synchronized <T> List<T> list(Serializable where) {
        return (List<T>) list(where.getClass(), prepareQuerySql(where));
    }

    @SuppressWarnings("all")
    public static synchronized <T> List<T> list(Class<T> entityCls, String sql, Object... params) {
        List<T> data = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection()) {
            PreparedStatement ps = conn.prepareStatement(sql);
            boolean isPreSql = sql.contains("?");
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
                sql = sql.replaceFirst("\\?", wrapSqlValue(params[i]) + "");
            }
            if (isPreSql) {
                log.info("QUERY SQL: {}", sql);
            }
            ResultSet rs = ps.executeQuery();
            Map<String, Field> entityMaps = getEntityMaps(entityCls);
            ResultSetMetaData metaData = rs.getMetaData();
            if (!isAnyMatched(entityMaps.keySet(), metaData)) {
                return Collections.emptyList();
            }
            while (rs.next()) {
                T obj = entityCls.getDeclaredConstructor().newInstance();
                for (int index = 1, count = metaData.getColumnCount(); index <= count; index++) {
                    String columnName = metaData.getColumnLabel(index);
                    Object value = rs.getObject(columnName);
                    Optional.ofNullable(entityMaps.get(columnName.toLowerCase())).ifPresent(field -> {
                        ReflectUtil.set(obj, field, value);
                    });
                }
                data.add(obj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return data;
    }

    public static int save(Serializable entity) {
        return doExecuteUpdate(prepareInsertSql(entity, id(entity)));
    }

    public static int update(Serializable update, Serializable where) {
        return doExecuteUpdate(prepareUpdateSql(update, where));
    }

    public static int delete(Serializable where) {
        return doExecuteUpdate(prepareDeleteSql(where));
    }

    public static List<Map<String, Object>> queryMetas(String dbname, String table) {
        String dbnameValues = "('" + dbname + "'," +
                "'" + dbname.toLowerCase() + "'," +
                "'" + dbname.toUpperCase() + "')";
        String tableValues = "('" + table + "'," +
                "'" + table.toLowerCase() + "'," +
                "'" + table.toUpperCase() + "')";

        String sql = "select * from information_schema.COLUMNS WHERE TABLE_SCHEMA IN " + dbnameValues + " AND TABLE_NAME IN " + tableValues;
        log.info("QUERY SQL: {}", sql);
        try (Connection conn = DBUtil.getConnection()) {
            List<Map<String, Object>> list = new ArrayList<>();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> data = new HashMap<>();
                for (int index = 1, count = metaData.getColumnCount(); index <= count; index++) {
                    data.put(metaData.getColumnLabel(index), rs.getObject(index));
                }
                list.add(data);
            }
            return list;
        } catch (Exception e) {
            log.error("rollback error", e);
            UIComponentUtil.errorAlert(e.getMessage());
        }
        return Collections.emptyList();
    }


    private static int doExecuteUpdate(String sql) {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            int i = conn.prepareStatement(sql).executeUpdate();
            conn.commit();
            return i;
        } catch (Exception e) {
            log.error("execute sql error", e);
            UIComponentUtil.errorAlert(e.getMessage());
            Optional.ofNullable(conn).ifPresent(connection -> {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    log.error("rollback error", e);
                    UIComponentUtil.errorAlert(e.getMessage());
                }
            });
        } finally {
            Optional.ofNullable(conn).ifPresent(connection -> {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.warn("close db connection error", e);
                }
            });
        }
        return 0;
    }

    private static String prepareQuerySql(Serializable where) {
        StringBuilder sql = new StringBuilder(DBConst.SELECT + getTableName(where.getClass()) + DBConst.WHERE);
        setWhereParams(where, sql);
        sql.replace(sql.length() - DBConst.AND.length(), sql.length(), DBConst.EMPTY);
        log.info("QUERY SQL: {}", sql);
        return sql.toString();
    }

    private static String prepareInsertSql(Serializable entity, Map<String, Object> id) {
        StringBuilder sql = new StringBuilder(DBConst.INSERT + getTableName(entity.getClass()) + DBConst.BRACKETS_START);
        StringBuilder values = new StringBuilder();
        getEntityMaps(entity.getClass()).forEach((k, filed) -> {
            sql.append(k).append(", ");
            Object value = ReflectUtil.get(entity, filed);
            if (filed.isAnnotationPresent(Id.class) && value == null && id != null) {
                values.append(wrapSqlValue(id.values().toArray()[0])).append(DBConst.COMMA);
            }
            values.append(wrapSqlValue(value)).append(DBConst.COMMA);
        });
        sql.replace(sql.length() - DBConst.COMMA.length(), sql.length(), DBConst.VALUES);
        values.replace(values.length() - DBConst.COMMA.length(), values.length(), DBConst.BRACKETS_END);
        sql.append(values);
        log.info("INSERT SQL: {}", sql);
        return sql.toString();
    }

    private static String prepareUpdateSql(Serializable updateEntity, Serializable whereEntity) {
        StringBuilder sql = new StringBuilder(DBConst.UPDATE + getTableName(updateEntity.getClass()) + DBConst.SET);
        StringBuilder where = new StringBuilder(DBConst.WHERE);
        getEntityMaps(updateEntity.getClass()).forEach((k, filed) -> {
            Optional.ofNullable(ReflectUtil.get(updateEntity, filed)).ifPresent(v -> sql.append(k).append(DBConst.EQ).append(wrapSqlValue(v)).append(DBConst.COMMA));
            Optional.ofNullable(ReflectUtil.get(whereEntity, filed)).ifPresent(v -> where.append(k).append(DBConst.EQ).append(wrapSqlValue(v)).append(DBConst.AND));
        });
        sql.replace(sql.length() - DBConst.COMMA.length(), sql.length(), DBConst.EMPTY);
        where.replace(where.length() - DBConst.AND.length(), where.length(), DBConst.EMPTY);
        sql.append(where);
        log.info("UPDATE SQL: {}", sql);
        return sql.toString();
    }

    private static String prepareDeleteSql(Serializable where) {
        StringBuilder sql = new StringBuilder(DBConst.DELETE + getTableName(where.getClass()) + DBConst.WHERE);
        setWhereParams(where, sql);
        sql.replace(sql.length() - DBConst.AND.length(), sql.length(), DBConst.EMPTY);
        log.info("DELETE SQL: {}", sql);
        return sql.toString();
    }

    private static void setWhereParams(Serializable where, StringBuilder sql) {
        getEntityMaps(where.getClass()).forEach((k, filed) -> Optional.ofNullable(ReflectUtil.get(where, filed)).ifPresent(v -> sql.append(k).append(DBConst.EQ).append(wrapSqlValue(v)).append(DBConst.AND)));
    }

    private static String getTableName(Class<?> entityCls) {
        Table table = entityCls.getAnnotation(Table.class);
        Assert.notNull(table, "[" + entityCls.getName() + "]must be use Annotation [@Table] mark table name.");
        return StringUtils.isNotEmpty(table.value()) ? table.value() : entityCls.getSimpleName();
    }

    private static Map<String, Object> id(Serializable entity) {
        List<Field> fields = ReflectUtil.getAllFields(entity.getClass()).stream().filter(field -> field.isAnnotationPresent(Id.class)).collect(Collectors.toList());
        Assert.state(fields.size() <= 1, "[" + entity.getClass().getName() + "]ID is not unique");
        if (fields.isEmpty()) {
            return null;
        }
        Field field = fields.get(0);
        String columName = field.getAnnotation(Id.class).value();
        Object value = ReflectUtil.get(entity, field);
        return Map.of(StringUtils.isEmpty(columName) ? field.getName() : columName, Objects.requireNonNullElseGet(value, System::nanoTime));
    }

    private static Object wrapSqlValue(Object value) {
        return value instanceof CharSequence ? "'" + value + "'" : value;
    }

    private static Map<String, Field> getEntityMaps(Class<?> entityCls) {
        return CACHE.computeIfAbsent(ENTITY_MAPPER_PREFIX + entityCls.getName(), k -> ReflectUtil.getAllFields(entityCls).stream().filter(field -> field.isAnnotationPresent(Id.class) || field.isAnnotationPresent(Column.class)).collect(Collectors.toMap(field -> {
            Column column = field.getAnnotation(Column.class);
            if (column != null) {
                return (StringUtils.isNotEmpty(column.value()) ? column.value() : field.getName()).toLowerCase();
            }
            String value = field.getAnnotation(Id.class).value();
            return (StringUtils.isNotEmpty(value) ? value : field.getName()).toLowerCase();
        }, x -> x)));
    }

    private static boolean isAnyMatched(Collection<String> properties, ResultSetMetaData metaData) throws SQLException {
        int count = metaData.getColumnCount();
        Set<String> columns = new HashSet<>();
        for (int index = 1; index <= count; index++) {
            columns.add(metaData.getColumnLabel(index).toLowerCase());
        }
        return properties.stream().anyMatch(columns::contains);
    }

}
